﻿using Lamex.Base;
using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Lamex.mDashboard.Common
{
    public class CURDHelper
    {
        public static string GetHNIFirst_Day(string HNIDate)
        {
            string strDate = "";
            string sql = @"SELECT [FIRST_DATE]
  FROM [LamexMESManager].[dbo].[Cost_HNI_Calendar]
  WHERE YEAR_MONTH='"+ HNIDate + "'";
            DataTable table = BaseBuildSql.GetDataTableBySql(sql);
            if (table.Rows.Count > 0)
                strDate = table.Rows[0]["FIRST_DATE"].ToString();
            return strDate;

        }
        public static DataTable GetTable_Aluminium(string strStart, string strEnd)
        {
            string sql = @"SELECT CONVERT(varchar(10),Scn_Date) Scn_Date ,a.Part_No ,Part_Desc,Drawing_No ,
                        b.MRP_CONTROLLER AS MRP,b.PRODUCT_ALLOCATION ,
                        CAST(InsideQty*Part_QTY/Order_QTY AS decimal(18,2)) AS Part_QTY,
                        CAST(InsideQty*Part_QTY/Order_QTY*COMB_TIME/60 AS decimal(18,2)) AS COMB_TIME
                        FROM View_OP_Inside_Detail AS a
                        LEFT JOIN IE_Part_MasterData AS b   ON a.Part_No=b.PART_NO
						LEFT JOIN MCG_PowderCoating_Type_Adjust ad on a.Part_No=ad.PART_NO
                        WHERE PROCESS_CODE='P21' AND MRP_CONTROLLER='P00'
                        AND Scn_Date>='" + strStart + "' AND  Scn_Date<='" + strEnd + "' " +
                        "AND ISNULL(AD.TYPE,'')!='钢柜'";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        public static DataTable GetTable_METAL_CABINET(string strStart, string strEnd)
        {
            string sql = @"SELECT  CONVERT(varchar(10),RE.Scn_Date) AS WORK_DATE,SUM(RE.InsideQty) AS METAL_CABINET_QTY  FROM Transfer_Scanning_record RE 
                        LEFT JOIN Transfer_Package_List LI ON RE.ProdOrder=LI.Package_Mo 
                        LEFT JOIN MCG_PowderCoating_Type_Adjust AD on LI.Material_No=ad.PART_NO
                        JOIN IE_Part_Routing RO ON RO.PART_NO=LI.Material_No
                        WHERE ISNULL(RE.Cancel_Flag,'')=''  AND LI.PROD_LINE='MCG' AND  ISNULL(AD.TYPE,'')!='铝材'
                        AND RO.GROUP_CODE='M011'AND RE.Scn_Date>='" + strStart + "' AND RE.Scn_Date<='" + strEnd + "' GROUP BY RE.Scn_Date";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        public static DataTable GetTable_Work_Hours(string strStart, string strEnd)
        {
            string sql = @"SELECT CONVERT(varchar(10),WORK_DATE) WORK_DATE ,[WORK_HOURS]
                          FROM [LamexMESManager].[dbo].[HR_Work_Hours_Powder_Coating]
                          WHERE WORK_DATE>='" + strStart + "' AND WORK_DATE<='" + strEnd + "'";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        public static DataTable GetTable_Productivity_History(string strHNIMonths)
        {
            string sql = @"SELECT  CONVERT(varchar(10),WORK_DATE) WORK_DATE
                          ,[ALUMINIUM_QTY]
                          ,[METAL_CABINET_QTY]
                          ,[MC_CONVERT_QTY]
                          ,[WORK_HOURS]
                          ,[PRODUCTIVITY]
                      FROM [LamexMESManager].[dbo].[MCG_PowderCoating_Productivity_History]
                      WHERE YEAR_MONTH='" + strHNIMonths+"'";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql);
            return dataTable;
        }
        public static bool Delete_History(string strHNIMonths)
        {
            string sql = @"  DELETE FROM  [LamexMESManager].[dbo].[MCG_PowderCoating_Productivity_History]
                         WHERE YEAR_MONTH='" + strHNIMonths + "'";
            int m = BaseBuildSql.ExecuteNonQuery(sql);
            if (m > 0)
                return true;
            else
                return false;
        }
        public static bool Update_History(string strHNIMonths)
        {
            string sql1 = @" SELECT YEAR_MONTH  FROM [LamexMESManager].[dbo].[MCG_PowderCoating_Productivity_History]
                         WHERE YEAR_MONTH='" + strHNIMonths + "' AND ISNULL(APPROVAL_TIME,'')!='' ";
            DataTable dataTable = BaseBuildSql.GetDataTableBySql(sql1);
            if (dataTable.Rows.Count > 0)
                return false;

            string sql = @"UPDATE  [LamexMESManager].[dbo].[MCG_PowderCoating_Productivity_History] SET APPROVAL_TIME=GETDATE()
                         WHERE YEAR_MONTH='" + strHNIMonths + "'";
            int m = BaseBuildSql.ExecuteNonQuery(sql);
            if (m > 0)
                return true;
            else
                return false;
        }
    }
}
